package bean;

import util.DBConnection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * This bean is used to fetch all the pub data and then construct the information
 * into HTML format.
 * 
 * @author Josef Hardi
 *
 */
public class BrowseBean {
	
	/** Database connection **/
	private Connection conn = null;
	
	/**
	 * Get result from select operation.
	 * 
	 * @return result string
	 */
	public String getResult() {
		Statement stmt = null;
		ResultSet result = null;
		
		// Get the database connection
	    conn = DBConnection.getConnection();
	    
	    try{
		    String query = "SELECT id, name, address, facility FROM Pub";
		    stmt  = conn.createStatement();
		    result  = stmt.executeQuery(query);
	    
		    // Return data in HTML form
		    return formatToHTML(result);
	    }
	    catch (SQLException e) {
	    	return e.toString();
	    } finally {
	    	try {
	            // Close everything.
	            if (result != null) result.close();
	            if (stmt != null) stmt.close();
	            if (conn != null) conn.close();
	    	} catch (Exception e) { // Trap all other exceptions
               return e.toString();
            }
	    }
	}

// ------------------------- PRIVATE METHODS ----------------------------  
	
	/*
	 * Convert the string result into HTML.
	 */
	private String formatToHTML(ResultSet rs) throws SQLException {
		
		// StringBuffer variable to hold the HTML String
	    StringBuffer buffer = new StringBuffer();

	    // Loop through the result set and format data.
	    if (rs == null || !rs.next()) {
	        // Display message when no countries exist
	        buffer.append("<br /><table bgcolor='red' align='center'>\n");
	        buffer.append("<tr><td><center><font color='white' size=4>\n");
	        buffer.append("<b>No Pub data available!</b></td></tr></table>");
	        
	        return buffer.toString();
	    } 
	    else {	    
	        // Get name of the columns from metadata.
	        buffer.append("<table border='1'>\n");
	        
	        ResultSetMetaData meta = rs.getMetaData();
	        int nCol = meta.getColumnCount();
	        for (int i = 2; i <= nCol; i++) {
	        	buffer.append("<th bgcolor='#000080'><font color='white'>" +
	                  meta.getColumnLabel(i) + "</font></th>");
	        }
	        
	        // Display Pub information
	        int count = 0;
	        do {
	        	String id = rs.getObject(1).toString();
	        	
	        	buffer.append("<tr>\n");
	        	for (int i = 2; i <= nCol; i++) {
	        		
	        		buffer.append("<td>");
	        		
	        		if (meta.getColumnLabel(i).equals("name")) 
	        			buffer.append("<a href='showpub?id=" + id + "'>" + rs.getObject(i) + "</a>");
	        		else
	        			buffer.append(rs.getObject(i));
		            buffer.append("</td>");
	            }   
	            
	            // Remove button
	            buffer.append("<td>");
	            buffer.append("<form action='removepub' method='post' style='margin: 0px; padding: 0px;>'");
	            buffer.append("<input type='submit' value='Remove' onclick='return checkDeleteItem();' />");
	            buffer.append("<input type='hidden' name='id' value='" + id + "' />");
	            buffer.append("</form>");
	            buffer.append("</td>");
	            
	        	buffer.append("</tr>\n");
	        	count++;
	        }	        
	        while (rs.next());
	        
	        // Add button
	        buffer.append("<form action='addpub' method='get'>\n");
	        buffer.append("<tr><td colspan="+nCol+">Total: "+count+" pubs found! <input type='submit' value='Add more!' />" +
	        		"<input type='hidden' name='latitude' value='46.495792513908995' />" +
	        		"<input type='hidden' name='longitude' value='11.353940963745117' /></td></tr>");
	        buffer.append("</form>");
	        buffer.append("</table>");
	        
	        return buffer.toString();
	    }
	}
}
